SQL LIKE operator

Probleem

In veel gevallen weten we maar half wat we willen zoeken. We kennen een deel van de naam, het begin of het einde of iets tussenin.

Oplossing

Met de LIKE operator kan je in SQL naar patronen zoeken (pattern matching). Je met de LIKE operator naar patronen in tekst zoeken. Je combineert de LIKE operator met jokers (wildcards). De set van karakters bestaat uit symbolen die één of meer ontbrekende tekens voorstellen.

De LIKE operator vergelijkt een tekstuitdrukking in het linkerlid, dat eventueel een wildcard bevat, met een patroon tussen aanhalingstekens in het rechterlid.

Een patroon is een veralgemeende tekstuitdrukking. d.w.z. een tekst waarin nog enige vrijheid bestaat. Zo staat ‘A*’ in het filesystem voor alle bestandsnamen die met een A beginnen, en staat "?eind" voor alle teksten van viif tekens waarbij het eerste teken volledig willekeurig is en de laatste vier letterlijk "eind" zijn.

De betekenis van de wildcards in SQL is als volgt:

Om alle boeken te selecteren waarvan de familienaam van de auteur begint met A:

-- ji
-- 11 september 2013
-- bestandsnaam: BoekenSelectLikeBegin.sql
use JefInghelbrecht;
-- selecteer alle titels van auteurs waarvan de familienaam
-- begint met een A
-- like is niet hoofdletter gevoelig omdat de collation
-- ingesteld is op SQL_Latin1_General_CP1_CI_AS ---> CI = case insensitive
select Voornaam, Familienaam, Titel
   from Boeken
   where Familienaam like 'a%';

Omwille van de ingestelde collation geeft het volgende sql statement hetzelfde resultaat:

select Voornaam, Familienaam, Titel
   from Boeken
   where Familienaam like 'A%';

Om alle boeken te selecteren waarvan de familenaam van de auteur eindigen op een s:

-- selecteer alle titels van auteurs waarvan de familienaam
-- eindigt met een S
-- like is niet hoofdletter gevoelig omdat de collation
-- ingesteld is op SQL_Latin1_General_CP1_CI_AS ---> CI = case insensitive
-- bestandsnaam: BoekenSelectLikeEnd.sql
select Voornaam, Familienaam, Titel
   from Boeken
   where Familienaam like '%s';

Om boeken waarvoor in de titel het woord economie voorkomt, te selecteren:

-- ji
-- 4 december 2012
-- een bepaald patroon in het midden van een tekst selecteren
-- bestandsnaam: BoekenSelectLikeInText.sql
use A88586JefInghelbrecht;

select Voornaam, Familienaam, Titel, Verschijningsjaar
   from Boeken
   where Titel like '%economie%';

Het is belangrijk dat je toepassing van de like operator en de wildcard ziet. Bijvoorbeeld:

-- ji
-- 18 september 2013
--
-- ik weet niet meer of het Matijs of Mathijs is. Welke query kan ik
-- schrijven er zeker van te zijn de naam terug te vinden?
use JefInghelbrecht;

insert into Boeken (
   Voornaam,
   Familienaam,
   Titel)
values (
   'Mathijs',
   'Degrote',
   'Leren werken met SQL')

select Voornaam from Boeken
   where Voornaam like 'ma%ijs'

Opdracht

In de bibliotheekapplicatie wil de gebruiker de mogelijkheid hebben om op een deel (bijvoorbeeld 'van') van een familienaam te zoeken. Schrijf een sql script die precies dat mogelijk maakt.

Bewaar de script in BoekenSelectLikeInFamilienaam.sql

Om alle boeken te selecteren waarvan de familienaam van de auteur begint met een A, R of C:

-- selecteer alle titels van auteurs waarvan de familienaam
-- begint met de letter A, R of C
-- like is niet hoofdletter gevoelig omdat de collation
-- ingesteld is op SQL_Latin1_General_CP1_CI_AS ---> CI = case insensitive
-- bestandsnaam: BoekenSelectLikeKarakterLijst.sql
select Voornaam, Familienaam, Titel
   from Boeken
   where Familienaam like 'a%' or Familienaam like 'r%' or Familienaam like 'c%';

In MSSQL kan je de logische expressie vervangen door een lijst van letters, door komma's gescheiden en tussen [ ]. Om alle boeken te selecteren van auteurs waarvan de familienaam begint met een A, B en C kan je het koppelteken gebruiken om aan te geven dat het een continue lijst (range) van karakters is:

-- bestandsnaam: BoekenSelectLikeRange.sql
use JefInghelbrecht
select Familienaam, Titel from boeken where Familienaam like '[A-C]%'

Om alle boeken te selecteren die verschenen zijn in de jaren negentig van de vorige eeuw:

use JefInghelbrecht;
select Familienaam, Titel
from boeken
   where Verschijningsjaar like '199_';

-- selecteer alle titels die verschenen zijn in het eerste jaar
-- van elk decennium van de 20 ste eeuw
-- bestandsnaam: BoekenSelectLike_.sql
select Voornaam, Familienaam, Titel, Verschijningsjaar
   from Boeken
   where Verschijningsjaar like '19_1'

Alle boeken die verschenen zijn in de jaren dertig tachtig en negentig van de 20ste eeuw:

select Voornaam,
    Familienaam,
    Verschijningsjaar
from Boeken
   where Verschijningsjaar like '198_' or like '193_' or like '199_'
   order by Verschijningsjaar desc, Familienaam asc

Escape Sequence

Als het percentteken (%) deel uitmaakt van de zoekstring in de LIKE moet je het escape teken (\) gebruiken om aan te geven dat het percentteken (%) geen deel van de instructie is maar wel degelijk een deel van de data. Hetzelfde geldt voor de wildcard _:

-- ji
-- 18 september 2013
-- bestandsnaam: BoekenSelectLikeEscapeSequence.sql
use JefInghelbrecht
go

select Titel, Familienaam from Boeken where Familienaam LIKE '%[a\%\_]%' {ESCAPE '\'}
go

Meer dan één teken voor het escape sequence levert een foutmelding op.

use JefInghelbrecht
go

insert into Boeken (
   Voornaam,
   Familienaam,
   Titel)
values (
   'Mathijs',
   'Degrote',
   'Leren 100% werken met SQL'
)
go

select Voornaam, Familienaam, Titel from Boeken
   where Titel = 'Leren 100% werken met SQL'

-- ik wil alle titels waarin 100% voorkomt
-- hier moet ik het percentteken escapen omdat
-- het in de like context geen percentteken
-- betekent maar een wildcard aangeeft
select Voornaam, Familienaam, Titel from Boeken
   where Titel like '%\%%' {ESCAPE '\'}

Dit levert de juiste resultatenset. Als je het escape teken weglaat krijg je een lijst van alle boeken:

select Voornaam, Familienaam, Titel from Boeken
   where Titel like '%%%' {ESCAPE '\'}

The escape sequence must be at the end of the SQL statement. For multiple SQL statements in a command string, the escape sequence needs to be at the end of each relevant SQL statement.

Nog een voorbeeld:

insert into Boeken (
   Voornaam,
   Familienaam,
   Titel,
   Stad,
   Uitgeverij,
   Verschijningsjaar)
values 
   ('Céline', 'De Groot', 'De 100% verwondering', 'Antwerpen', 'Manteau', '1970'),
   ('Celine' ,'Rademaekers', 'Jagen en gejaagd worden 20%', 'Antwerpen', 'De Bezige Bij', '1954'),
   ('CELINE', 'Smiley', 'Het zijn en het 50% niets', 'Parijs', 'Gallimard', '1943')

-- alle boeken met in de titel het woord en
select * from Boeken where Titel like '% en %'

-- alle boeken waarin het letterteken % inkomt
select * from Boeken where Titel like '%\%%'